<?php
    /**
     * Created by PhpStorm.
     * User: fang
     * Date: 2018/1/16
     * Time: 下午3:31
     */

    namespace yaliSDK\utils;

//批量导入数据
    use think\Db;

    class LoadDataHelper {
        /**
         * @param array $data
         * @return bool|\think\response\Json
         * 批量导入数据
         * $val[] = ['test'=>'1111','addtime'=>time()]
         * $data[] = ['val'=>$val,'table'=>'yali_table']
         * $data[] = ['val'=>数据数组2,'table'=>数据表名2]
         */
        public static function load_data(array $data) {
            try {
                if (empty($data)) return false;
                $file_name = uniqid();//创建文件
                //把数据生成sql
                $status = true;
                foreach ($data as $key => $value) {
                    $value['type'] = $value['type']??'insert';
                    switch ($value['type']){
                        case 'insert':
                            $status = self::create_sql($value['val'], ['table' => $value['table'], 'file_name' => $file_name]);
                            if (false == $status) {
                                //一个失败,删除生成的sql文件,并终止遍历
                                self::del_sql($file_name);
                                return false;
                            }
                            break;
                        case 'update':
                            $status = self::update_sql($value['val'], ['table' => $value['table'], 'file_name' => $file_name]);
                            if (false == $status) {
                                //一个失败,删除生成的sql文件,并终止遍历
                                self::del_sql($file_name);
                                return false;
                            }
                            break;
                    }
                }
                //导入文件
                if (true == $status) {
//                   / return true;
                    return self::import_sql($file_name);
                }
                return false;
            } catch (\Exception $e) {
                return false;
            }
        }

        /**
         * @param array $result 需要创建sql的数据
         * @param array $info 表信息
         * @param bool $is_table_info 是否需要生产检查表的信息
         * @return bool
         * 根据内容创建对应的sql信息
         */
        public static function create_sql($result = [], $info = [], $is_table_info = false) {
            if (empty($result) || empty($info['table']) || empty($info['file_name'])) return false;
            try {
                set_time_limit(0);
                $sqlStr = '';
                $table = $info['table'];
                //是否需要指定的表头信息
                if ($is_table_info) {
                    $sqlStr = "SET FOREIGN_KEY_CHECKS=0;\r\n";
                    $sqlStr .= "DROP TABLE IF EXISTS `$table`;\r\n";
                    $create = db()->execute('show create table ' . $table);
                    $sqlStr .= $create['0']['Create Table'] . ";\r\n";
                    $sqlStr .= "\r\n";
                }
                if (!empty($result)) {
                    $count = count($result);
                    foreach ($result as $key => $vo) {
                        $keys = array_keys($vo);
                        $keys = array_map('addslashes', $keys);
                        $keys = join('`,`', $keys);
                        $keys = "`" . $keys . "`";
                        $vals = array_values($vo);
                        $vals = array_map('addslashes', $vals);
                        $vals = join("','", $vals);
                        $vals = "'" . $vals . "'";
                        if ($key == 0 && $count > 1) {
                            $sqlStr .= "insert ignore  into `$table`($keys) values($vals),";
                        } elseif ($key == 0 && $count == 1) {
                            $sqlStr .= "insert ignore  into `$table`($keys) values($vals);\r\n";
                        } elseif ($key == ($count - 1)) {
                            $sqlStr .= "($vals);\r\n";
                        } else {
                            $sqlStr .= "($vals),";
                        }
                    }
                    mkdirs('./sqls/');
                    $filename = './sqls/' . $info['file_name'] . '.sql';
                    $fp = fopen($filename, 'a+'); //指定文件追加内容
                    fputs($fp, $sqlStr);
                    fclose($fp);
                }
                return true;
            } catch (\Exception $e) {
                return false;
            }
        }

        public static function update_sql($result = [], $info = [],$pk='id') {
            if (empty($result) || empty($info['table']) || empty($info['file_name'])) return false;
            try {
                set_time_limit(0);
                $sqlStr = '';
                $table = $info['table'];
                if (!empty($result)) {
//                    $count = count($result);
                    foreach ($result as $key => $vo) {
                        $keys = array_keys($vo);
                        $keys = array_map('addslashes', $keys);
                        $sqlStr .="UPDATE `$table` SET ";
                        foreach ($keys as $k=>$v){
                            $sqlStr .= "`" . $v . "`='".$vo[$v]."',";
                        }
                        $sqlStr = rtrim($sqlStr,',');
                        $sqlStr .= " WHERE `{$pk}`='".$vo[$pk]."';\r\n";
                    }
                    mkdirs('./sqls/');
                    $filename = './sqls/' . $info['file_name'] . '.sql';
                    $fp = fopen($filename, 'a+'); //指定文件追加内容
                    fputs($fp, $sqlStr);
                    fclose($fp);
                }
                return true;
            } catch (\Exception $e) {
                return false;
            }
        }

        /**
         * @param string $filename
         * @return bool|\think\response\Json
         * 导入指定的SQL
         */
        public static function import_sql($filename = '') {
            try {
                set_time_limit(0);
                if (!file_exists('./sqls/' . $filename . ".sql")) {
                    return false;
//                return json(['code' => -1, 'data' => '', 'msg' => '数据不存在!']);
                }
                $sqls = self::analysisSql('./sqls/' . $filename . '.sql');
                if (empty($sqls)) return false;
                $db = Db::connect(config('database.db_config1'));
                foreach ($sqls as $key => $sql) {
                    $db->execute($sql);
                }
                self::del_sql($filename);
                return true;
            } catch (\Exception $e) {
                return false;
            }
        }

        /**
         * @param string $filename
         * @return bool
         * 删除指定的文件
         */
        public static function del_sql($filename = '') {
            try {
                unlink('./sqls/' . $filename . '.sql');
                return true;
            } catch (\Exception $e) {
                return false;
            }
        }

        /**
         * 解析sql文件
         * @param $file
         * @return array
         */
        public static function analysisSql($file) {
            try {
                // sql文件包含的sql语句数组
                $sqls = array();
                $f = fopen($file, "rb");
                // 创建表缓冲变量
                $create = '';
                while (!feof($f)) {
                    // 读取每一行sql
                    $line = fgets($f);
                    // 如果包含空白行，则跳过
                    if (trim($line) == '') {
                        continue;
                    }
                    // 如果结尾包含';'(即为一个完整的sql语句，这里是插入语句)，并且不包含'ENGINE='(即创建表的最后一句)，
                    if (!preg_match('/;/', $line, $match) || preg_match('/ENGINE=/', $line, $match)) {
                        // 将本次sql语句与创建表sql连接存起来
                        $create .= $line;
                        // 如果包含了创建表的最后一句
                        if (preg_match('/ENGINE=/', $create, $match)) {
                            // 则将其合并到sql数组
                            $sqls [] = $create;
                            // 清空当前，准备下一个表的创建
                            $create = '';
                        }
                        // 跳过本次
                        continue;
                    }
                    $sqls [] = $line;
                }
                fclose($f);

                return $sqls;
            } catch (\Exception $e) {
                return false;
            }
        }
    }